<?php
class xl_category{
    function update($data){
        global $dbh;
        if($data['image'] !=''){
            $sql='UPDATE categories SET category_name=:category_name,type_category=:type_category,location=:location,parent=:parent,image=:image,shows=:shows,description=:description,keyword=:keyword,title=:title,day_time=:day_time WHERE id=:id';
        } else {
            $sql='UPDATE categories SET category_name=:category_name,type_category=:type_category,location=:location,parent=:parent,shows=:shows,description=:description,keyword=:keyword,title=:title,day_time=:day_time WHERE id=:id';
            unset($data['image']);
        }
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    function read_id_parent($id){
        global $dbh;
        $sql='SELECT * FROM categories WHERE parent=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        $category=$sth->fetch(PDO::FETCH_ASSOC);
        return $category;
    }
    function test_name($alias){
        global $dbh;
        $sql='SELECT alias FROM categories WHERE alias=:alias';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':alias'=>$alias));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function test($id,$category_name){
        global $dbh;
        $sql='SELECT id,category_name FROM categories WHERE id!=:id AND category_name=:category_name';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id,':category_name'=>$category_name));
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }
    function test_type($id){
        global $dbh;
        $sql='SELECT type_category FROM categories WHERE id=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function delete_all($id){
        global $dbh;
        $sql='SELECT * FROM categories WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function delete($id){
        global $dbh;
        $sql='DELETE FROM categories WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
    }
     function delete_post($id){
        global $dbh;
        $sql='DELETE FROM posts WHERE category_id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
    }
    function delete_categories($id){
        global $dbh;
        $sql='SELECT image,day_time FROM posts WHERE category_id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }
     function read($id){
        global $dbh;
        $sql='SELECT * FROM categories WHERE id=:id LIMIT 0,1';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return  $sth->fetch(PDO::FETCH_ASSOC);
    }
    function read_post($id){
        global $dbh;
        $sql='SELECT * FROM posts WHERE category_id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return  $sth->fetchAll(PDO::FETCH_ASSOC);
    }
    function read_list(){
        global $dbh;
        $sql='SELECT cate.* ,COUNT(pos.id) AS count FROM posts pos RIGHT JOIN categories cate ON cate.id=pos.category_id  GROUP BY cate.category_name ORDER BY cate.id DESC';
        $sth=$dbh->prepare($sql);
        $sth->execute();
        return $sth->fetchAll();
    }
     function read_id_cate($id){
        global $dbh;
        $sql='SELECT * FROM categories WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth->fetch(PDO::FETCH_ASSOC);
    }
    function updatde_shows($id){
        global $dbh;
        $sql='UPDATE categories SET shows=1-shows WHERE id=:id';
        $sth=$dbh->prepare($sql);
        $sth->execute(array(':id'=>$id));
        return $sth;
        
    }
    function insert($data){
        global $dbh;
        $sql="INSERT INTO categories(category_name,alias,type_category,location,parent,image,shows,description,keyword,title,day_time) VALUE (:category_name,:alias,:type_category,:location,:parent,:image,:shows,:description,:keyword,:title,:day_time)";
        $sth=$dbh->prepare($sql);
        $sth->execute($data);
        return $sth;
    }
    
    
    
   
    
    
}